package com.ridbor001.demo.dao;

import com.ridbor001.demo.util.RDBDataStore;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

public class RDBStudentCourseDAO {
    RDBDataStore dataStore = new RDBDataStore();


    public List<StudentCourseRelation> getAllStudentCourseRelations() throws SQLException {
        String sql = "SELECT student_id, course_id, grade_id FROM student_courses";
        List<StudentCourseRelation> relations = new ArrayList<>();

        try (Connection conn = dataStore.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                relations.add(new StudentCourseRelation(
                        rs.getString("student_id"),
                        rs.getString("course_id"),
                        rs.getString("grade_id")  // 可能为null
                ));
            }
        }
        return relations;
    }




    // 添加学生-课程关联（公开方法）
    public  void addStudentToCourse(String studentId, String courseId)
            throws SQLException {
        String sql = "INSERT INTO student_courses (student_id, course_id) " +
                "VALUES (?, ?)";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            pstmt.setString(2, courseId);
            pstmt.executeUpdate();
        }
    }
    // 根据学生ID或课程ID获取关系记录
    public   List<StudentCourseRelation> getRelationsByStudentOrCourse(
            Set<String> studentIds, Set<String> courseIds) throws SQLException {

        // 检查输入是否为空
        boolean noStudents = studentIds == null || studentIds.isEmpty();
        boolean noCourses = courseIds == null || courseIds.isEmpty();

        if (noStudents && noCourses) {
            return Collections.emptyList();
        }

        List<StudentCourseRelation> relations = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT student_id, course_id, grade_id FROM student_courses WHERE "
        );

        List<String> conditions = new ArrayList<>();
        List<String> params = new ArrayList<>();

        // 如果有学生ID
        if (!noStudents) {
            String studentPlaceholders = studentIds.stream()
                    .map(id -> "?")
                    .collect(Collectors.joining(","));
            conditions.add("student_id IN (" + studentPlaceholders + ")");
            params.addAll(studentIds);
        }

        // 如果有课程ID
        if (!noCourses) {
            String coursePlaceholders = courseIds.stream()
                    .map(id -> "?")
                    .collect(Collectors.joining(","));
            conditions.add("course_id IN (" + coursePlaceholders + ")");
            params.addAll(courseIds);
        }

        // 组合条件
        sql.append(String.join(" OR ", conditions));

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setString(i + 1, params.get(i));
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    relations.add(new StudentCourseRelation(
                            rs.getString("student_id"),
                            rs.getString("course_id"),
                            rs.getString("grade_id")  // 可能为null
                    ));
                }
            }
        }
        return relations;
    }
    // 根据学生ID获取关系记录
    public   List<StudentCourseRelation> getRelationsByStudent(String studentId) throws SQLException {
        String sql = "SELECT student_id, course_id, grade_id FROM student_courses WHERE student_id = ?";
        List<StudentCourseRelation> relations = new ArrayList<>();

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, studentId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    relations.add(new StudentCourseRelation(
                            studentId,  // 直接使用参数值
                            rs.getString("course_id"),
                            rs.getString("grade_id")
                    ));
                }
            }
        }
        return relations;
    }
    // 更新学生课程关系中的成绩ID
    public  boolean updateStudentCourseGrade(String studentId, String courseId, String gradeId)
            throws SQLException {

        String sql = "UPDATE student_courses SET grade_id = ? "
                + "WHERE student_id = ? AND course_id = ?";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, gradeId);
            pstmt.setString(2, studentId);
            pstmt.setString(3, courseId);

            return pstmt.executeUpdate() > 0;
        }
    }

}
